* This code creates datasets of changes in imported input shares and measures of tariffs
* at the 2-digit and 4-digit level.

* 4-digit level dataset

* Process 4-digit ISIC Rev 2 USA tariffs applied to Mexico
import delimited "../AggregateData/Database/WITS/input/USACANISICR2.csv", clear
keep reportername product productname tariffyear dutytype weightedaverage
ren tariffyear year
ren product sector
keep if reportername=="United States"
keep if dutytype=="AHS"
drop dutytype
local t0 = 1993
keep if year==`t0' | year==1999
rename weightedaverage weighted
gen dum=0
replace dum=1 if year==`t0'
sort sector year
gen weighted`t0'_temp = weighted * dum
bys sector: egen weighted`t0' = total(weighted`t0'_temp)
drop weighted`t0'_temp dum
drop if year==`t0'
drop year
gen dweighted = weighted - weighted`t0' 
gen gweighted = dweighted / weighted`t0' * 100 
replace gweighted=0 if dweighted==0 
drop weighted*
save "../AggregateData/Database/WITS/temp/dtariffs_USA_4d.dta", replace

* Process 4-digit ISIC Rev 2 Mexico tariffs applied to USA
import delimited "../AggregateData/Database/WITS/input/MEXfromUSACANisicR2.csv", clear
keep partnername product productname tariffyear dutytype weightedaverage
ren tariffyear year
ren product sector
keep if partnername=="United States"
keep if dutytype=="AHS"
drop dutytype
local t0 = 1991
keep if year==`t0' | year==1999
rename weightedaverage weighted
gen dum=0
replace dum=1 if year==`t0'
sort sector year
gen weighted`t0'_temp = weighted * dum
bys sector: egen weighted`t0'= total(weighted`t0'_temp)
drop weighted`t0'_temp dum
drop if year==`t0'
drop year
gen dweighted_mex = weighted - weighted`t0' 
gen gweighted_mex = dweighted_mex / weighted`t0' * 100 
replace gweighted_mex=0 if dweighted_mex==0 
drop weighted*
save "../AggregateData/Database/WITS/temp/dtariffs_Mex_4d.dta", replace

* Process EIA data
* Create for each ISIC Rev 4-digit sector the growth in import share
* between 1994 and 1999
use "temp/EIA_clean.dta", clear
destring year, replace
destring id, replace
tostring clase, replace
ren sector sector2
gen sector = substr(clase,1,4)
destring sector, replace
order id year sector
local t0 = 1994
drop if year>2000
xtset id year
tsfill, full
bys year sector: egen matprod = total(mat)
gen mj = matprod / totalmat
bys year sector: egen totalmatfor = total(matfor)
gen sAGGj = totalmatfor / matprod
bys year: gen sAGG = totalmatfor / totalmat
collapse (mean) sAGGj mj, by(year sector)
gen i94 = 0
replace i94=1 if year==`t0'
gen mj94b = 0
replace mj94b = . if i94==1 & mj==.
replace mj94b = mj * i94
bys sector: egen mj94 = total(mj94b)
replace mj94=. if mj94b==. & mj94==0
drop mj94b
gen sAGGj94b = 0
replace sAGGj94b = . if i94==1 & sAGGj==.
replace sAGGj94b = sAGGj * i94
bys sector: egen sAGGj94 = total(sAGGj94b)
replace sAGGj94=. if sAGGj94b==. & sAGGj94==0
drop sAGGj94b
drop if sector==.
drop i94
gen deltas = sAGGj - sAGGj94
gen growths = deltas/ sAGGj94 * 100
keep if year==1999
drop year sAGGj mj mj94 sAGGj94

* Merge with USA tariff data
merge 1:1 sector using "../AggregateData/Database/WITS/temp/dtariffs_USA_4d.dta"
keep if _merge==3
drop _merge
merge 1:1 sector using "../AggregateData/Database/WITS/temp/dtariffs_Mex_4d.dta"
keep if _merge==3
drop _merge
save  "temp/Database_NAFTA_EIA_4d.dta", replace


* 2-digit level dataset

* Process EIA data
* Create for each ISIC Rev 2-digit sector (31 to 39) the growth in import share
* between 1993 and 1999

use "temp/EIA_clean.dta", clear
local t0 = 1993
destring year, replace
destring id, replace
tostring clase, replace
drop sector
gen sector2 = substr(clase,1,2)
gen sector3 = substr(clase,1,3)
gen sector4 = substr(clase,1,4)
order id year sector2 sector3 sector4
order id year sector2 sector3 sector4
qui do "input/concordanceR2toR3.do" // Add ISIC Rev 3 sectors as grouped in OECD input output tables
drop if year>2000
xtset id year
tsfill, full
bys year sector: egen matprod = total(mat)
gen mj = matprod / totalmat
bys year sector: egen totalmatfor = total(matfor)
gen sAGGj = totalmatfor / matprod
bys year: gen sAGG = totalmatfor / totalmat
collapse (mean) sAGGj mj, by(year sector)
gen i94 = 0
replace i94=1 if year==`t0'
gen mj94b = 0
replace mj94b = . if i94==1 & mj==.
replace mj94b = mj * i94
bys sector: egen mj94 = total(mj94b)
replace mj94=. if mj94b==. & mj94==0
drop mj94b
gen sAGGj94b = 0
replace sAGGj94b = . if i94==1 & sAGGj==.
replace sAGGj94b = sAGGj * i94
bys sector: egen sAGGj94 = total(sAGGj94b)
replace sAGGj94=. if sAGGj94b==. & sAGGj94==0
drop sAGGj94b
drop if sector==.
drop i94
gen deltasi = sAGGj - sAGGj94
gen growthsi = deltasi/ sAGGj94 * 100
keep if year==1999
drop year sAGGj mj mj94 sAGGj94
merge 1:1 sector using "../AggregateData/Database/temp/buyersellertariffs.dta" // merge with tariff change data 1995-1999
keep if sector>=3 & sector<=18
drop _merge
merge 1:1 sector using "../AggregateData/Database/OECD/temp/sectornames.dta"
keep if _merge==3
drop _merge
drop sector_name
ren sector_name2 sector_name
ren dweighted_usa dt_usa
qui do "../AggregateData/Database/OECD/CreateDescription.do"
merge 1:1 sector using "../AggregateData/Database/OECD/temp/VAshares.dta" // add share in tota value added
keep if _merge==3
drop _merge
merge 1:1 sector using "../AggregateData/Database/WITS/temp/dtariffs_MexToUS.dta" // add Mexican tariff changes
keep if _merge==3
drop _merge
ren dweighted dt_mex
qui do "../AggregateData/Database/NAFTA/input/CreateLabels.do"
save  "temp/Database_NAFTA_EIA.dta", replace




